package dao;

import beans.Account;

import java.math.BigDecimal;
import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class AccountDAO extends ConnBean {

	private Connection connection = null;

	/**
	 * 获取账号链表
	 * @return
	 */
	public List<Account> selectAll() {
		Statement statement = null;
		ResultSet resultSet = null;

		ArrayList<Account> list = new ArrayList<Account>();
		try {
			connection = getConnection();
			if (connection == null)
				System.out.println("error");
			statement = connection.createStatement();
			resultSet = statement.executeQuery("select * from account");
			while (resultSet.next()) {
				Account account = new Account();
				account.setAno(resultSet.getInt(1));
				account.setPassWord(resultSet.getString(2));
				account.setAccountName(resultSet.getString(3));
				account.setSex(resultSet.getString(4));
				account.setBirthdate(resultSet.getString(5));
				account.setPosition(resultSet.getString(6));
				account.setDepartment(resultSet.getString(7));
				account.setPhoneNumber(resultSet.getString(8));
				account.setAddress(resultSet.getString(9));
				account.setAccountState(resultSet.getInt(10));
				account.setSetTime(resultSet.getString(11));
				account.setDeleteTime(resultSet.getString(12));
				list.add(account);
			}
			statement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (connection != null && (!connection.isClosed())) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return list;
	}

	/**
	 * 获取账号对象
	 * @param idNum
	 * @return
	 */
	public Account getAccount(String ano) {
		Statement statement = null;
		ResultSet resultSet = null;
		Account account = new Account();
		try {
			connection = getConnection();
			if (connection == null)
				System.out.println("error");
			String sql = "select * from Account where ano=" +Integer.parseInt(ano);
			statement = connection.createStatement();
			resultSet = statement.executeQuery(sql);
			if (resultSet.next()) {
				System.out.println("get");
				account.setAno(resultSet.getInt(1));
				account.setPassWord(resultSet.getString(2));
				account.setAccountName(resultSet.getString(3));
				account.setSex(resultSet.getString(4));
				account.setBirthdate(resultSet.getString(5));
				account.setPosition(resultSet.getString(6));
				account.setDepartment(resultSet.getString(7));
				account.setPhoneNumber(resultSet.getString(8));
				account.setAddress(resultSet.getString(9));
				account.setAccountState(resultSet.getInt(10));
			} else {
				System.out.println("null");
				account = null;
			}
			statement.close();
			connection.close();
			//return user;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (connection != null && (!connection.isClosed())) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		return account;
	}


	/**
	 * 插入账号
	 * @param account
	 * @return
	 */
	public int insertAccount(Account account) {
		PreparedStatement preparedStatement = null;
		String sql = "insert into Account values(?,?,?,?,?,?,?,?,?)";
		int result = 0;
		if (account == null)
			return result;
		int number = 0;
		try {
			connection = getConnection();

			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setInt(1, account.getAno());
			preparedStatement.setString(2, account.getPassWord());
			preparedStatement.setString(3, account.getAccountName());
			preparedStatement.setString(4, account.getSex());
			preparedStatement.setString(5, account.getBirthdate());
			preparedStatement.setString(6, account.getPosition());
			preparedStatement.setString(7, account.getdepartment());
			preparedStatement.setString(8, account.getPhoneNumber());
			preparedStatement.setString(9, account.getAddress());

			number = preparedStatement.executeUpdate();
			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (connection != null && (!connection.isClosed())) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		//System.out.println("***"+number);
		if (number > 0)
			result = 1;
		return result;
	}

	/**
	 * 删除账号
	 * @param account
	 * @return
	 */
	public int deleteAccount(Account account) {
		PreparedStatement preparedStatement = null;
		String sql = "update Account set account_state=?,delete_Time=?" + "where id_Num=" + account.getAno();
		int result = 0;
		try {
			connection = getConnection();
			preparedStatement = connection.prepareStatement(sql);
			preparedStatement.setLong(1, account.getAccountState());
			preparedStatement.setString(2, account.getDeleteTime());
			result = preparedStatement.executeUpdate();
			preparedStatement.close();
			connection.close();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (connection != null && (!connection.isClosed())) {
					connection.close();
				}
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
		if (result > 0)
			return 1;
		else
			return 0;
	}



	/**
	 * 更新账号
	 * @param account
	 * @return
	 */
    public int updateAccount(Account account) {
		PreparedStatement preparedStatement=null;
		String sql="update Account set passWord=?,account_name=?,phone_Number=?"+"where id_Num="+account.getAno();
		int result=0;
		try{
			connection=getConnection();
			preparedStatement=connection.prepareStatement(sql);
			preparedStatement.setInt(1, account.getAno());
			preparedStatement.setString(2, account.getAccountName());
			preparedStatement.setString(3, account.getPhoneNumber());
			result=preparedStatement.executeUpdate();
			preparedStatement.close();
			connection.close();	
		}catch (SQLException e) {
			e.printStackTrace();
		} finally{
			try{
				if (connection!=null && (!connection.isClosed())){
					connection.close();
				}
			}catch(SQLException e){
				e.printStackTrace();
			}
		}
		if(result>0)
			return 1;
		else
			return 0;
	}
}
